﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;

namespace OALPTool
{
    public partial class FrmOLAPTool : Form
    {
        #region 自定义变量
        private int ret, sysret;
        private string strMsg;
        private DataView dvExcel = null;
        #endregion

        #region 初始化

        public FrmOLAPTool()
        {
            InitializeComponent();
        }

        #endregion

        #region 界面事件函数

        /// <summary>
        /// 工具栏
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void tb_Menu_ButtonClick(object sender, ToolBarButtonClickEventArgs e)
        {
            if (e.Button.Text == "退出")
            {
                this.Close();
            }
            else if (e.Button.Text == "导出")
            {
                ret = OutToExcel(this.dgvTmplate, out strMsg);
                if (ret < 0)
                {
                    TSysPublic.ShowMsg(strMsg);
                    return;
                }

            }
            else if (e.Button.Text == "导入")
            {

            }
        }

        /// <summary>
        /// 选择文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSelFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog file = new OpenFileDialog();
            file.Title = "请选择Excel文件";
            file.Filter = "Excel文件(*.xls)|*.xls";
            if (file.ShowDialog() == DialogResult.OK)
            {
                this.tbFilePath.Text = file.FileName;
                try
                {
                    ArrayList sheets = ExcelSheetName(file.FileName.Trim());
                    this.cmbSheet.Items.Clear();
                    for (int i = 0; i < sheets.Count; i++)
                    {
                        this.cmbSheet.Items.Add(sheets[i].ToString().Trim());
                    }
                    if (this.cmbSheet.Items.Count > 0)
                    {
                        this.cmbSheet.SelectedIndex = 0;
                    }
                }
                catch (Exception ex)
                {
                    TSysPublic.ShowErrMsg(ex.Message);
                }
            }
            else
            {
                return;
            }
        }

        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnInput_Click(object sender, EventArgs e)
        {
            if (dvExcel != null)
            {
                dvExcel = null;
                dgvTmplate.DataSource = null;
            }
            if (this.tbFilePath.Text == "" || this.cmbSheet.Text == "")
            {
                TSysPublic.ShowMsg("请输入需要导入excel的路径和sheet表名！");
                return;
            }
            //读取Excel数据
            string sExcelFile = this.tbFilePath.Text.Trim();
            string sConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + sExcelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
            OleDbConnection connection = new OleDbConnection(sConnectionString);
            string strLinkSql = "Select * from [" + this.cmbSheet.Text.Trim() + "$]";

            OleDbDataAdapter adp = new OleDbDataAdapter(strLinkSql, connection);

            DataSet ds = new DataSet();
            try
            {
                adp.Fill(ds, "Book1");
            }
            catch (Exception ex)
            {
                TSysPublic.ShowMsg("读取表底示数数据不成功：" + ex.Message);

                return;
            }
            if (ds == null || ds.Tables.Count < 0 || ds.Tables[0].Rows.Count < 0)
            {
                TSysPublic.ShowMsg("读取表底示数数据为空！");

                return;
            }

            dvExcel = new DataView(ds.Tables[0]);

            //设置Excel格式
            ret = formatExcelData();
            if (ret < 0)
            {
                TSysPublic.ShowMsg("设置格式失败！");
                return;
            }

            dgvTmplate.AutoGenerateColumns = false;
            dgvTmplate.DataSource = dvExcel;
        }

        #endregion

        #region 辅助函数
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="DGV"></param>
        /// <param name="strErr"></param>
        /// <returns></returns>
        private int OutToExcel(DataGridView DGV, out string strErr)
        {
            strErr = "";
            ArrayList argsExcel = new ArrayList();
            object oprret = null;
            if (DGV.RowCount <= 0)
            {
                strErr = "没有可导出的数据！";
                return -1997;
            }

            ArrayList alFormat = new ArrayList();
            foreach (DataGridViewColumn dgvc in DGV.Columns)
            {
                alFormat.Add(dgvc.DefaultCellStyle.Format);
                dgvc.DefaultCellStyle.Format = "";
            }

            Cursor.Current = Cursors.WaitCursor;
            new DataGridViewToExcel().OALP_DataGridViewToExcel(DGV, null);
            Cursor.Current = Cursors.Default;
            if (ret < 0)
            {
                strErr = "导出出错!";
                return ret;
            }

            for (int i = 0; i < alFormat.Count; i++)
                DGV.Columns[i].DefaultCellStyle.Format = alFormat[i].ToString();

            return 0;
        }
        /// <summary>
        /// Excel连接串
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <returns></returns>
        internal string con2Excel(string strFilePath)
        {
            return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties=\"Excel 8.0;\"";
        }
        /// <summary>
        /// 获得Excel中的所有sheetname
        /// </summary>
        /// <param name="strFileName"></param>
        /// <returns></returns>
        internal ArrayList ExcelSheetName(string strFilePath)
        {
            ArrayList al = new ArrayList();
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(con2Excel(strFilePath));
            conn.Open();
            DataTable sheetNames = conn.GetOleDbSchemaTable
                (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            conn.Close();
            foreach (DataRow dr in sheetNames.Rows)
            {
                al.Add(dr[2].ToString().Replace("$", "").Replace("'", ""));
            }
            return al;
        }
        /// <summary>
        /// 设置读取格式
        /// </summary>
        /// <returns></returns>
        private int formatExcelData()
        {
            //ArrayList colList = new ArrayList();
            //colList.Add("客户名称");
            //colList.Add("费用科目");
            //colList.Add("科目明细");
            //colList.Add("销售金额");
            //colList.Add("费用金额");
            //colList.Add("费率");
            //foreach (DataColumn dcHeader in dvExcel.Table.Columns)
            //{
            //    if (!colList.Contains(dcHeader.ColumnName))
            //    {
            //        TSysPublic.ShowMsg("列 " + dcHeader.ColumnName + " 列头名称不合规范，请检查！");
            //        return -1;
            //    }
            //}

            try
            {
                dvExcel.Table.Columns["区域"].ColumnName = "Zone";
                dvExcel.Table.Columns["城市"].ColumnName = "City";
                dvExcel.Table.Columns["级别"].ColumnName = "SellLevel";
                dvExcel.Table.Columns["销售类别"].ColumnName = "SellKind";
                dvExcel.Table.Columns["客户名"].ColumnName = "Customer";
                dvExcel.Table.Columns["客户名称"].ColumnName = "CustomerName";
                dvExcel.Table.Columns["费用科目"].ColumnName = "AsName";
                dvExcel.Table.Columns["科目明细"].ColumnName = "AsItemName";
                dvExcel.Table.Columns["销售金额"].ColumnName = "SellAmount";
                dvExcel.Table.Columns["费用金额"].ColumnName = "ExpenseAmount";
                dvExcel.Table.Columns["费率"].ColumnName = "Rates";

                if (!dvExcel.Table.Columns.Contains("ListNo"))
                {
                    dvExcel.Table.Columns.Add("ListNo", typeof(int));
                }

                if (!dvExcel.Table.Columns.Contains("CNFlag"))
                {
                    dvExcel.Table.Columns.Add("CNFlag", typeof(string));
                }
                if (!dvExcel.Table.Columns.Contains("SumFlag"))
                {
                    dvExcel.Table.Columns.Add("SumFlag", typeof(string));
                }

                if (!dvExcel.Table.Columns.Contains("Formula"))
                {
                    dvExcel.Table.Columns.Add("Formula", typeof(string));
                }
                int ListNo = 3;
                string CustomerName = "";
                foreach (DataRowView drExcel in dvExcel)
                {
                    drExcel["ListNo"] = ListNo++;
                    string CustomerNameTemp = drExcel["CustomerName"].ToString().Trim();
                    if (!string.IsNullOrEmpty(CustomerNameTemp))
                    {
                        if (!CustomerNameTemp.Contains("汇总"))
                        {
                            CustomerName = CustomerNameTemp;
                            drExcel["CNFlag"] = "0";
                        }
                        else
                        {
                            CustomerName = "";
                            drExcel["CNFlag"] = "1";

                        }
                    }
                    else
                    {
                        drExcel["CustomerName"] = CustomerName;
                        drExcel["CNFlag"] = "0";
                    }
                }
                dvExcel.Sort = "ListNo";
                dvExcel.Table.AcceptChanges();

                string strWhere = "CNFlag='1'";
                DataRow[] drrHZ = dvExcel.Table.Select(strWhere);


                foreach (DataRow drHZ in drrHZ)
                {
                    decimal SellAmount = TMath.ConvertToDecimal(drHZ["SellAmount"]);
                    string CustomerNameHZ = drHZ["CustomerName"].ToString().Trim();
                    int ListNoHz = TMath.ConvertToInt(drHZ["ListNo"]);
                    foreach (DataRowView drExcel in dvExcel)
                    {
                        string SumFlag = drExcel["SumFlag"].ToString().Trim();
                        string CName = drExcel["CustomerName"].ToString().Trim();
                        int ListNoExcel = TMath.ConvertToInt(drExcel["ListNo"]);
                        if (!CName.Contains("汇总"))
                        {
                            CName = drExcel["CustomerName"].ToString().Trim() + " 汇总";

                        }
                        if (CustomerNameHZ == CName && string.IsNullOrEmpty(SumFlag))
                        {
                            if (SellAmount > TMath.P_FINANCE_ZERO_2)
                            {
                                decimal ExpenseAmount = TMath.ConvertToDecimal(drExcel["ExpenseAmount"]);
                                drExcel["Rates"] = (ExpenseAmount / SellAmount);
                                drExcel["SumFlag"] = (ExpenseAmount / SellAmount).ToString("P");
                                drExcel["Formula"] = "=Y" + ListNoExcel + "/$X$" + ListNoHz;

                            }
                            else
                            {
                                drExcel["Rates"] = 0m;
                                drExcel["SumFlag"] = "0.0%";
                            }
                        }
                    }
                    dvExcel.Table.AcceptChanges();
                }

            }
            catch (Exception ex)
            {
                TSysPublic.ShowMsg("导入数据字段有问题：" + ex.Message);
                return -1;
            }
            return 0;
        }
        #endregion
    }
}
